package web.zipcode;

import java.io.FileReader;
import java.io.BufferedReader;
import java.io.IOException;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.StringTokenizer;

import common.misc.StringMisc;

/*
    seq NUMBER( 5 ) NOT NULL,
    zipcode CHAR( 7 ) NOT NULL,
    sido VARCHAR2( 10 ) NOT NULL,
    gugun VARCHAR2( 100 ) NOT NULL,
    dong VARCHAR2( 200 ) NOT NULL,
    ri VARCHAR2( 1000 ),
    bunji VARCHAR2( 150 ),
    CONSTRAINT PK_zipcode PRIMARY KEY ( seq )
*/
public class RemakeZipcode {

    private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl";
    private static final String DB_USER = "dog";
    private static final String DB_PASSWD = "dog";

    private static final String ZIPCODE_FILE_NAME = "C:\\KorZipcode.txt";
    private static final String DELIMINATER = "|";

    public RemakeZipcode() throws ClassNotFoundException {

        Class.forName( DB_DRIVER );

    } // end constructor

    public void dropTable() throws SQLException {

        Connection con = null;
        Statement stmt = null;

        try {
System.out.println("1");
            con = DriverManager.getConnection( DB_URL, DB_USER, DB_PASSWD );
System.out.println("2");
            stmt = con.createStatement();

            String query = "DROP TABLE zipcode CASCADE CONSTRAINTS";

            System.out.println( "> " + query );
            stmt.executeUpdate( query );

        } catch ( SQLException sqle ) {

            throw sqle;

        } finally {

            if ( stmt != null ) stmt.close();
            if ( con != null ) con.close();

        } // end try~catch~finally

    } // end method

    public void createTable() throws SQLException {

        Connection con = null;
        Statement stmt = null;

        try {

            con = DriverManager.getConnection( DB_URL, DB_USER, DB_PASSWD );
            stmt = con.createStatement();

            String query = "CREATE TABLE zipcode ( seq NUMBER( 5 ) NOT NULL, zipcode CHAR( 7 ) NOT NULL, sido VARCHAR2( 10 ) NOT NULL, gugun VARCHAR2( 100 ) NOT NULL, dong VARCHAR2( 200 ) NOT NULL, ri VARCHAR2( 1000 ), bunji VARCHAR2( 150 ) )";

            System.out.println( "> " + query );
            stmt.executeUpdate( query );

        } catch ( SQLException sqle ) {

            throw sqle;

        } finally {

            if ( stmt != null ) stmt.close();
            if ( con != null ) con.close();

        } // end try~catch~finally

    } // end method

    public void insertRows() throws SQLException, IOException {

        Connection con = null;
        Statement stmt = null;

        BufferedReader br = null;

        try {

            con = DriverManager.getConnection( DB_URL, DB_USER, DB_PASSWD );
            stmt = con.createStatement();

            br = new BufferedReader( new FileReader( ZIPCODE_FILE_NAME ) );

            String query = null;

            StringTokenizer tokens = null;

            String line = null;
            
            String zipcode = null;
            String sido = null;
            String gugun = null;
            String dong = null;
            String ri = null;
            String bunji = null;
            int seq = 0;

            int count = 0;

            System.out.println( ">>>>> start insert rows" );
            while ( ( line = br.readLine() ) != null ) {

                tokens = new StringTokenizer( line, DELIMINATER );

                zipcode = StringMisc.fixNull( tokens.nextToken() ).trim();
                sido = StringMisc.fixNull( tokens.nextToken() ).trim();
                gugun = StringMisc.fixNull( tokens.nextToken() ).trim();
                dong = StringMisc.fixNull( tokens.nextToken() ).trim();
                ri = StringMisc.fixNull( tokens.nextToken() ).trim();
                bunji = StringMisc.fixNull( tokens.nextToken() ).trim();
                seq = Integer.parseInt( tokens.nextToken() );

                query = new StringBuffer( "INSERT INTO zipcode ( seq, zipcode, sido, gugun, dong, ri, bunji ) VALUES ( " ).append( seq ).append( ", " ).append( StringMisc.makeField( zipcode ) ).append( ", " ).append( StringMisc.makeField( sido ) ).append( ", " ).append( StringMisc.makeField( gugun ) ).append( ", " ).append( StringMisc.makeField( dong ) ).append( ", " ).append( StringMisc.makeField( ri ) ).append( ", " ).append( StringMisc.makeField( bunji ) ).append( " )" ).toString();

                stmt.executeUpdate( query );

                if ( ( ++count % 1000 ) == 0 )
                    System.out.println( ">>>>> [" + count + "] records inserted" );

            } // end while

            System.out.println( ">>>>> [" + count + "] records inserted" );

        } catch ( IOException ioe ) {

            throw ioe;

        } catch ( SQLException sqle ) {

            throw sqle;

        } finally {

            if ( stmt != null ) stmt.close();
            if ( con != null ) con.close();

            if ( br != null ) br.close();

        } // end try~catch~finally

    } // end method

    public static void main( String [] args ) throws Exception {

        RemakeZipcode make = new RemakeZipcode();

        try {

            make.dropTable();

        } catch ( SQLException sqle ) {}

        make.createTable();
        make.insertRows();

    } // end main

} // end class